package com.hzdl.car.mapper;

import com.hzdl.car.bean.BaseStat;
import com.hzdl.car.bean.Rent;
import com.hzdl.car.bean.RentExcel;
import com.hzdl.car.sqlprovider.RentSqlProvider;
import com.hzdl.car.vo.RentVo;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface RentMapper {
    @Insert("insert into bus_rent(rentId,price,beginDate,rentFlag,identity,carNumber,openName,createTime) " +
            "values(#{rentId},#{price},#{beginDate},#{rentFlag},#{identity},#{carNumber},#{openName},now()) ")
    int addRent(RentVo rentVo);
    @SelectProvider(type = RentSqlProvider.class,method = "findRent")
    List<Rent> findRent(RentVo rentVo);
    @Delete("delete from bus_rent where rentId=#{rentId}")
    int delRent(RentVo rentVo);
    @Update("update bus_rent set price=#{price},beginDate=#{beginDate}" +
            "where rentId=#{rentId}")
    int updateRent(RentVo rentVo);
    @Select("select * from bus_rent where rentId=#{rentId}")
    Rent findCarNById(RentVo rentVo);
    @Update("update bus_rent set rentFlag=#{rentFlag},returnDate=now() where rentId=#{rentId}")
    int RentIsFlag(RentVo rentVo);
    //年度销售额
    @Select("select openName name, sum(price) value from bus_rent where year(createTime) = #{year} group by openName")
    List<BaseStat> findPriceByYear(Integer year);
    //公司年度月销售额
    @Select("select IFNULL(sum(price),0) from bus_rent where year(createTime)=#{year} and month(createTime)=1\n" +
            "union ALL\n" +
            "select IFNULL(sum(price),0) from bus_rent where year(createTime)=#{year} and month(createTime)=2\n" +
            "union ALL\n" +
            "select IFNULL(sum(price),0) from bus_rent where year(createTime)=#{year} and month(createTime)=3\n" +
            "union ALL\n" +
            "select IFNULL(sum(price),0) from bus_rent where year(createTime)=#{year} and month(createTime)=4\n" +
            "union ALL\n" +
            "select IFNULL(sum(price),0) from bus_rent where year(createTime)=#{year} and month(createTime)=5\n" +
            "union ALL\n" +
            "select IFNULL(sum(price),0) from bus_rent where year(createTime)=#{year} and month(createTime)=6\n" +
            "union ALL\n" +
            "select IFNULL(sum(price),0) from bus_rent where year(createTime)=#{year} and month(createTime)=7\n" +
            "union ALL\n" +
            "select IFNULL(sum(price),0) from bus_rent where year(createTime)=#{year} and month(createTime)=8\n" +
            "union ALL\n" +
            "select IFNULL(sum(price),0) from bus_rent where year(createTime)=#{year} and month(createTime)=9\n" +
            "union ALL\n" +
            "select IFNULL(sum(price),0) from bus_rent where year(createTime)=#{year} and month(createTime)=10\n" +
            "union ALL\n" +
            "select IFNULL(sum(price),0) from bus_rent where year(createTime)=#{year} and month(createTime)=11\n" +
            "union ALL\n" +
            "select IFNULL(sum(price),0) from bus_rent where year(createTime)=#{year} and month(createTime)=12")
    List<String> companyPriceByYear(Integer year);
    //客户出租单显示
    @Select("select r.rentId,r.price,r.beginDate,r.returnDate,r.carNumber,r.openName,c.custName from bus_rent r inner join bus_customer c on r.identity=c.identity where rentId=#{rentId}")
    RentExcel findRentExcel(String rentId);
}
